package distriKub;

import java.sql.*;

public class Database {
	
	Connection con; // The connection to be used with the SQLite database
	Statement stmt;
	
	public Database(){ // Constructor
		
		try {
			Class.forName("org.sqlite.JDBC");
			
			// Making the connection (opening the SQLite database)
			
			con = DriverManager.getConnection("jdbc:sqlite:database\\distrikub.db");
			stmt = con.createStatement();
			
			}catch (ClassNotFoundException CNFExp) {			
				System.err.println("\n==> Database.java ==> Could not load the appropriate library for using SQlite. (Exiting...) \n");
				System.exit(0);
			 }catch (SQLException SQLExp) {			
					System.err.println("\n==> Database.java ==> Could not load the SQlite driver. (Exiting...) \n");
					SQLExp.printStackTrace();
					System.exit(0);
				 }
			 
	} // End of Database constructor
	
	public boolean registerNewPlayer(String  playerId, String userName, String password){ // TODO : Returns false if the player ID or UserName is already taken
		boolean alreadyExists = false;
		ResultSet result = runQuestionQuery("SELECT COUNT(*) FROM Players WHERE PlayerId=" + "'" + playerId + "'");
		try {
			int rowsReturned = result.getInt(1);
			if (rowsReturned!=0)
				alreadyExists = true;
			else { // No such user ID or UserName (UserName was chosen to be unique for convenience purposes only)
				
				// Tested OK
				
				runUpdateQuery("INSERT INTO Players (PlayerId,UserName,Password,Winnings) VALUES (" + "'" + playerId + "'" + ", '" + userName + "', '" + password + "', 0)"); // Format : PlayerId,UserName,Password,Winnings
			
			} // End of else block
				
		} catch (SQLException e) {e.printStackTrace();}
		return !alreadyExists;
	} // End of registerNewPlayer
	
	public void registerGameResults(boolean afterCrash, String gameSignature, String winnerId, String winnerName, String[] gameLosers ){
		
		if (isGameSignatureAlreadyExists(gameSignature)) 
			return; // Some other player has already updated these game results
		else{
			runUpdateQuery("INSERT INTO GameData (PlayerId,WinnerId,WinnerName,GameSignature) VALUES (" + winnerId + ", '" + winnerId + "', '" + winnerName + "', '" + gameSignature + "')"); // Format : PlayerId,UserName,Password,Winnings
		}
		
	} // End of registerGameResults()
	
	public String getAllRegisteredPlayerNames(){
		StringBuilder resultAsLongString = new StringBuilder();
		ResultSet result = runQuestionQuery("SELECT UserName FROM Players");
		try{
			while (result.next()){
				resultAsLongString.append(result.getString(1)+",");
			} // End of while block
			
			resultAsLongString.deleteCharAt(resultAsLongString.length()-1); // Deletes the last comma because it is unnecessary
			
		}catch (SQLException SQLExp){System.out.println("===> Database.java ===> getAllRegisteredPlayerNames\n"); SQLExp.printStackTrace();}
		
		return resultAsLongString.toString();
	} // End of getAllRegisteredPlayerNames()
	
	private ResultSet runQuestionQuery(String theQuery){ // TODO : Returns NULL if SQLException was thrown
		
		ResultSet returnedResults = null;
		
		try {
			returnedResults = stmt.executeQuery(theQuery);
		} catch (SQLException SQLExp) {
			System.out.println("\n==> Database.java ==> (runQuestionQuery) SQLException : " + SQLExp.getMessage() + "\n");
			SQLExp.printStackTrace();
		}
		
		return returnedResults;
	} // End of runQuestionQuery
	
	private int runUpdateQuery(String theQuery){
		int rowsAffected = 0;
		
		try {
			rowsAffected = stmt.executeUpdate(theQuery);
		} catch (SQLException SQLExp) {
			System.out.println("\n==> Database.java ==> (runUpdateQuery) SQLException : " + SQLExp.getMessage() + "\n");
			SQLExp.printStackTrace();
		}
		
		return rowsAffected;
	} // End of runUpdateQuery
	
	private boolean isGameSignatureAlreadyExists(String theGameSignature){
		boolean alreadyExists = false;
		ResultSet result = runQuestionQuery("SELECT COUNT(*) FROM GameData WHERE GameSignature=" + theGameSignature);
		try {
			int rowsReturned = result.getInt(1);
			if (rowsReturned!=0)
				alreadyExists = true;
			else { 
				alreadyExists = false;
			} // End of else block
				
		} catch (SQLException e) {e.printStackTrace();}
		
		return alreadyExists;
	}
	
	public boolean Login(String username, String password){
		ResultSet result = runQuestionQuery("SELECT count(*) FROM Players where UserName='"+username+"' and Password='"+password+"'");
		try{
			int count = result.getInt(1);
			if (count == 1)
				return true;
			else
				return false;
		}catch (SQLException SQLExp){System.out.println("===> Database.java ===> Login\n"); SQLExp.printStackTrace();}
		
		return false;
	} // End of getAllRegisteredPlayerNames()
	
	public void closeDB(){
		try {
			if (stmt!=null)
				System.out.println("\nClosing database statement...");
				stmt.close();
			if (con!=null)
				System.out.println("\nClosing database connection...");
				con.close();
		} catch (SQLException e) {
			System.err.println("\n==> Database.java ==> Error while closing the database.\n");
		}
	} // End of closeDB

}
